﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_BGM_ProjectReport : IDAL_BGM_ProjectReport
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        //

        public D_BGM_ProjectReport()
        {

        }

        public IList<M_BGM_ProjectReport> GetFull(Nullable<int> bgm_proj_seq
            , Nullable<int> bgm_proj_exp_seq, Nullable<int> rpt_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectReport> infoList = new List<M_BGM_ProjectReport>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(2, "@bgm_proj_exp_seq", bgm_proj_exp_seq);
                dbManager.AddParameters(3, "@rpt_type", rpt_type);

                strSQL = "SELECT pj.bgm_proj_rpt_seq, pj.bgm_proj_exp_seq"//, pj.bgm_rec_seq"
                    //+ ", pj.bgm_trans_from_seq, pj.bgm_trans_to_seq"
                    //+ ", pj.icm_ppr_item_seq, pj.icm_pr_item_seq, pj.icm_po_item_seq, pj.icm_dn_item_seq"
                    + ", pj.bgm_proj_oper_exp_seq, pj.used_amount, pj.cal_amount, pj.rpt_type"

                    + " FROM bgm_project_report pj"
                    + " LEFT JOIN bgm_project_expense pjexp ON pj.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                    + " WHERE pj.record_status not like @delete_status"
                    + (bgm_proj_seq != null ? " and pjexp.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (bgm_proj_exp_seq != null ? " and pj.bgm_proj_exp_seq = @bgm_proj_exp_seq" : "")
                    + (rpt_type != null ? " and pj.rpt_type = @rpt_type" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectReport info = new M_BGM_ProjectReport();
                        info.bgm_proj_rpt_seq = Convert.ToInt32(drc[i]["bgm_proj_rpt_seq"]);
                        info.bgm_proj_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_exp_seq"]);
                        //info.bgm_rec_seq = cn.ConvertNullInt(drc[i]["bgm_rec_seq"]);
                        //info.bgm_trans_from_seq = cn.ConvertNullInt(drc[i]["bgm_trans_from_seq"]);
                        //info.bgm_trans_to_seq = cn.ConvertNullInt(drc[i]["bgm_trans_to_seq"]);
                        //info.icm_ppr_item_seq = cn.ConvertNullInt(drc[i]["icm_ppr_item_seq"]);
                        //info.icm_pr_item_seq = cn.ConvertNullInt(drc[i]["icm_pr_item_seq"]);
                        //info.icm_po_item_seq = cn.ConvertNullInt(drc[i]["icm_po_item_seq"]);
                        //info.icm_dn_item_seq = cn.ConvertNullInt(drc[i]["icm_dn_item_seq"]);
                        info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_oper_exp_seq"]);
                        info.used_amount = cn.ConvertNullDouble(drc[i]["used_amount"]);
                        info.cal_amount = cn.ConvertNullDouble(drc[i]["cal_amount"]);
                        info.rpt_type = cn.ConvertNullInt(drc[i]["rpt_type"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectReport> GetList(Nullable<int> bgm_proj_seq
            , Nullable<int> bgm_proj_exp_seq, Nullable<int> rpt_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectReport> infoList = new List<M_BGM_ProjectReport>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(2, "@bgm_proj_exp_seq", bgm_proj_exp_seq);
                dbManager.AddParameters(3, "@rpt_type", rpt_type);

                strSQL = "SELECT pj.bgm_proj_exp_seq"
                    + ", sum(coalesce(pj.cal_amount, 0)) as cal_amount"

                    + " FROM bgm_project_report pj"
                    + " LEFT JOIN bgm_project_expense pjexp ON pj.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                    + " WHERE pj.record_status not like @delete_status"
                    + (bgm_proj_seq != null ? " and pjexp.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (bgm_proj_exp_seq != null ? " and pj.bgm_proj_exp_seq = @bgm_proj_exp_seq" : "")
                    + (rpt_type != null ? " and pj.rpt_type = @rpt_type" : "")
                    + " ORDER BY pj.bgm_proj_exp_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectReport info = new M_BGM_ProjectReport();
                        info.bgm_proj_exp_seq = Convert.ToInt32(drc[i]["bgm_proj_exp_seq"]);
                        info.cal_amount = cn.ConvertNullDouble(drc[i]["cal_amount"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectReport> GetCalculateList(Nullable<int> bgm_proj_seq
            , Nullable<int> bgm_proj_exp_seq, Nullable<int> rpt_type, Nullable<int> bgm_rec_seq
            , Nullable<int> bgm_trans_from_seq, Nullable<int> bgm_trans_to_seq
            , Nullable<int> icm_ppr_item_seq, Nullable<int> icm_pr_item_seq
            , Nullable<int> icm_po_item_seq, Nullable<int> icm_dn_item_seq
            , Nullable<int> bgm_proj_oper_exp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectReport> infoList = new List<M_BGM_ProjectReport>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(12);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(2, "@bgm_proj_exp_seq", bgm_proj_exp_seq);
                dbManager.AddParameters(3, "@rpt_type", rpt_type);
                dbManager.AddParameters(4, "@bgm_rec_seq", "");
                dbManager.AddParameters(5, "@bgm_trans_from_seq", "");
                dbManager.AddParameters(6, "@bgm_trans_to_seq", "");
                dbManager.AddParameters(7, "@icm_ppr_item_seq", "");
                dbManager.AddParameters(8, "@icm_pr_item_seq", "");
                dbManager.AddParameters(9, "@icm_po_item_seq", "");
                dbManager.AddParameters(10, "@icm_dn_item_seq", "");
                dbManager.AddParameters(11, "@bgm_proj_oper_exp_seq", bgm_proj_oper_exp_seq);

                strSQL = "SELECT pj.bgm_proj_exp_seq"
                    + ", sum(coalesce(pj.cal_amount, 0)) as cal_amount"

                    + " FROM bgm_project_report pj"
                    + " LEFT JOIN bgm_project_expense pjexp ON pj.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                    + " WHERE pj.record_status not like @delete_status"
                    + (bgm_proj_seq != null ? " and pjexp.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (bgm_proj_exp_seq != null ? " and pj.bgm_proj_exp_seq = @bgm_proj_exp_seq" : "")
                    + (rpt_type != null ? " and pj.rpt_type = @rpt_type" : "")
                    //+ (bgm_rec_seq != null ? " and coalesce(pj.bgm_rec_seq, -1) not like @bgm_rec_seq" : "")
                    //+ (bgm_trans_from_seq != null ? " and coalesce(pj.bgm_trans_from_seq, -1) not like @bgm_trans_from_seq" : "")
                    //+ (bgm_trans_to_seq != null ? " and coalesce(pj.bgm_trans_to_seq, -1) not like @bgm_trans_to_seq" : "")
                    //+ (icm_ppr_item_seq != null ? " and coalesce(pj.icm_ppr_item_seq, -1) not like @icm_ppr_item_seq" : "")
                    //+ (icm_pr_item_seq != null ? " and coalesce(pj.icm_pr_item_seq, -1) not like @icm_pr_item_seq" : "")
                    //+ (icm_po_item_seq != null ? " and coalesce(pj.icm_po_item_seq, -1) not like @icm_po_item_seq" : "")
                    //+ (icm_dn_item_seq != null ? " and coalesce(pj.icm_dn_item_seq, -1) not like @icm_dn_item_seq" : "")
                    + (bgm_proj_oper_exp_seq != null ? " and coalesce(pj.bgm_proj_oper_exp_seq, -1) not like @bgm_proj_oper_exp_seq" : "")
                    + " ORDER BY pj.bgm_proj_exp_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectReport info = new M_BGM_ProjectReport();
                        info.bgm_proj_exp_seq = cn.ConvertNullInt(drc[i]["bgm_proj_exp_seq"]);
                        info.cal_amount = cn.ConvertNullDouble(drc[i]["cal_amount"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_BGM_ProjectReport GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_BGM_ProjectReport info = new M_BGM_ProjectReport();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT bgm_proj_rpt_seq, bgm_proj_exp_seq"//, bgm_rec_seq"
                    //+ ", bgm_trans_from_seq, bgm_trans_to_seq"
                    //+ ", icm_ppr_item_seq, icm_pr_item_seq, icm_po_item_seq, icm_dn_item_seq"
                    + ", bgm_proj_oper_exp_seq, used_amount, cal_amount, rpt_type"

                    + " FROM bgm_project_report"
                    + " WHERE bgm_proj_rpt_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.bgm_proj_rpt_seq = Convert.ToInt32(drc[0]["bgm_proj_rpt_seq"]);
                    info.bgm_proj_exp_seq = cn.ConvertNullInt(drc[0]["bgm_proj_exp_seq"]);
                    //info.bgm_rec_seq = cn.ConvertNullInt(drc[0]["bgm_rec_seq"]);
                    //info.bgm_trans_from_seq = cn.ConvertNullInt(drc[0]["bgm_trans_from_seq"]);
                    //info.bgm_trans_to_seq = cn.ConvertNullInt(drc[0]["bgm_trans_to_seq"]);
                    //info.icm_ppr_item_seq = cn.ConvertNullInt(drc[0]["icm_ppr_item_seq"]);
                    //info.icm_pr_item_seq = cn.ConvertNullInt(drc[0]["icm_pr_item_seq"]);
                    //info.icm_po_item_seq = cn.ConvertNullInt(drc[0]["icm_po_item_seq"]);
                    //info.icm_dn_item_seq = cn.ConvertNullInt(drc[0]["icm_dn_item_seq"]);
                    info.bgm_proj_oper_exp_seq = cn.ConvertNullInt(drc[0]["bgm_proj_oper_exp_seq"]);
                    info.used_amount = cn.ConvertNullDouble(drc[0]["used_amount"]);
                    info.cal_amount = cn.ConvertNullDouble(drc[0]["cal_amount"]);
                    info.rpt_type = cn.ConvertNullInt(drc[0]["rpt_type"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public void Insert(M_BGM_ProjectReport info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@bgm_proj_rpt_seq", info.bgm_proj_rpt_seq);
                dbManager.AddParameters(1, "@descr", info.descr);
                dbManager.AddParameters(2, "@bgm_proj_exp_seq", info.bgm_proj_exp_seq);
                dbManager.AddParameters(3, "@bgm_rec_seq", "");
                dbManager.AddParameters(4, "@bgm_trans_from_seq", "");
                dbManager.AddParameters(5, "@bgm_trans_to_seq", "");
                dbManager.AddParameters(6, "@bgm_proj_oper_exp_seq", info.bgm_proj_oper_exp_seq);
                dbManager.AddParameters(7, "@used_amount", info.used_amount);
                dbManager.AddParameters(8, "@cal_amount", info.cal_amount);
                dbManager.AddParameters(9, "@rpt_type", info.rpt_type);
                
                dbManager.AddParameters(10, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(11, "@created_by", info.created_by);
                dbManager.AddParameters(12, "@created_when", info.created_when);

                strSQL = "INSERT INTO bgm_project_report"
                    + "(descr, bgm_proj_exp_seq"//, bgm_rec_seq, bgm_trans_from_seq, bgm_trans_to_seq"
                        //+ ", icm_ppr_item_seq, icm_pr_item_seq, icm_po_item_seq, icm_dn_item_seq"
                        + ", bgm_proj_oper_exp_seq, used_amount, cal_amount, rpt_type"
                        + ", record_status, created_by, created_when)"
                    + " VALUES (@descr, @bgm_proj_exp_seq"//, @bgm_rec_seq, @bgm_trans_from_seq, @bgm_trans_to_seq"
                        //+ ", @icm_ppr_item_seq, @icm_pr_item_seq, @icm_po_item_seq, @icm_dn_item_seq"
                        + ", @bgm_proj_oper_exp_seq, @used_amount, @cal_amount, @rpt_type"
                        + ", @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_BGM_ProjectReport info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(12);

                dbManager.AddParameters(0, "@bgm_proj_rpt_seq", info.bgm_proj_rpt_seq);
                dbManager.AddParameters(1, "@descr", info.descr);
                dbManager.AddParameters(2, "@bgm_proj_exp_seq", info.bgm_proj_exp_seq);
                dbManager.AddParameters(3, "@bgm_rec_seq", "");
                dbManager.AddParameters(4, "@bgm_trans_from_seq", "");
                dbManager.AddParameters(5, "@bgm_trans_to_seq", "");
                dbManager.AddParameters(6, "@bgm_proj_oper_exp_seq", info.bgm_proj_oper_exp_seq);
                dbManager.AddParameters(7, "@used_amount", info.used_amount);
                dbManager.AddParameters(8, "@cal_amount", info.cal_amount);
                dbManager.AddParameters(9, "@rpt_type", info.rpt_type);

                dbManager.AddParameters(10, "@updated_by", info.updated_by);
                dbManager.AddParameters(11, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_report"
                    + " set descr = @descr"
                    + ", bgm_proj_exp_seq = @bgm_proj_exp_seq"
                    //+ ", bgm_rec_seq = @bgm_rec_seq"
                    //+ ", bgm_trans_from_seq = @bgm_trans_from_seq"
                    //+ ", bgm_trans_to_seq = @bgm_trans_to_seq"
                    + ", bgm_proj_oper_exp_seq = @bgm_proj_oper_exp_seq"
                    + ", used_amount = @used_amount"
                    + ", cal_amount = @cal_amount"
                    + ", rpt_type = @rpt_type"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_rpt_seq = @bgm_proj_rpt_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_BGM_ProjectReport info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@bgm_proj_rpt_seq", info.bgm_proj_rpt_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_report"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_rpt_seq = @bgm_proj_rpt_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateStatus(M_BGM_ProjectReport info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", info.bgm_proj_rpt_seq);
                dbManager.AddParameters(1, "@record_status", info.record_status);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE bgm_project_report"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_rpt_seq = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
